#include "dbhelper.h"
#include <QSqlDatabase>

#include <QVariant>
#include <QApplication>

DbHelper::DbHelper()
{
    QString path=QApplication::applicationDirPath ();
    QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE");
    path.append("/resource/db.s3db");
    db.setDatabaseName(path);
    if(!db.open())
        db.lastError();


}

int DbHelper::SetSqlStrNonQuery(const QString &sql,QHash<QString,QString> param)
{
    QSqlQuery query;
    query.prepare(sql);
    QHashIterator<QString,QString> i(param);
    while (i.hasNext())
    {
        i.next();
        query.bindValue(":"+i.key(),i.value());
    }
    bool b=query.exec();
    if(b)
        return 1;
    else
    {
        query.lastError();
        return 0;
    }
}

int DbHelper::Insert(const QString &tablename, QHash<QString, QString> list,bool returnval)
{
    QString sql,s1,s2;
    sql="insert into "+tablename+"(";
    QHashIterator<QString,QString> i(list);
    QHash<QString, QString> param;
    int n=0;
    while (i.hasNext())
    {
        i.next();
        if(n==0)
        {
        s1+=i.key();
        if(isSysTime(i.value()))
            s2+="datetime('now','+8 hour')";
        else
        s2+=":"+i.key()+",";
        }
        else
        {
            s1+=","+i.key();
            if(isSysTime(i.value()))
                s2+=",datetime('now','+8 hour')";
            else
            s2+=",:"+i.key();
        }
        if(!isSysTime(i.key()))
        param[i.key()]=i.value();
    }
    sql+=s1+") values ("+s2+");";
    if(returnval)
    {
        sql+="select last_insert_rowid();";
        return GetSqlStrScalar(sql,param).toInt();
    }
    else
    return SetSqlStrNonQuery(sql,param);
}

void DbHelper::Update(const QString &tablename, QHash<QString, QString> list, QHash<QString, QString> where)
{

    QString sql;
    sql="update "+tablename+" set ";
    QHashIterator<QString,QString> i(list);
    QHash<QString, QString> param;
    int n=0;
    while (i.hasNext())
    {
        i.next();
        if(n==0)
        {
            sql+=i.key()+"=";
        if(isSysTime(i.value()))
            sql+="datetime('now','+8 hour')";
        else
        sql+=":"+i.key()+",";
        }
        else
        {
            sql+=","+i.key()+"=";
            if(isSysTime(i.value()))
                sql+=",datetime('now','+8 hour')";
            else
            sql+=",:"+i.key();
        }
        if(!isSysTime(i.key()))
        param[i.key()]=i.value();
    }
    sql+=" where ";
     i=QHash<QString, QString>(where);
     n=0;
     while (i.hasNext())
     {
         i.next();
         if(n==0)
         {
             sql+=i.key()+"=:"+i.key();
         }
         else
             sql+=" and "+i.key()+"=:"+i.key();
         param[i.key()]=i.value();
     }
     SetSqlStrNonQuery(sql,param);
}

void DbHelper::Delete(const QString &tablename, QHash<QString, QString> where)
{
    QString sql="delete from "+tablename+" where ";
    QHash<QString, QString> param;
     QHashIterator<QString,QString> i(where);
     int n=0;
     while (i.hasNext())
     {
         i.next();
         if(n==0)
         {
             sql+=i.key()+"=:"+i.key();
         }
         else
             sql+=" and "+i.key()+"=:"+i.key();
         param[i.key()]=i.value();
     }
     SetSqlStrNonQuery(sql,param);
}

QString DbHelper::GetSqlStrScalar(const QString &sql, QHash<QString, QString> param)
{
    QSqlQuery query;
    query.prepare(sql);
    QHashIterator<QString,QString> i(param);
    while (i.hasNext())
    {
        i.next();
        query.bindValue(":"+i.key(),i.value());
    }
    query.exec();
    while(query.next())
    {
        return query.value(0).toString();
    }
    return "";
}

QSqlQuery DbHelper::GetSqlStrQuery(const QString &sql, QHash<QString, QString> param)
{
    QSqlQuery query;
    query.prepare(sql);
    QHashIterator<QString,QString> i(param);
    while (i.hasNext())
    {
        i.next();
        query.bindValue(":"+i.key(),i.value());
    }
    query.exec();
    return query;
}

QString DbHelper::exchangeparam(const QString &param)
{
    if(param.startsWith('@'))
        return"";
    return"";
}

bool DbHelper::isSysTime(const QString &s)
{
    return s=="@systime";
}
